﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using PiscoSourDominio;
using PiscoSourSOAPRestServices.Persistencia;
using Dapper;

namespace PiscoSourPersistencia
{
    public class ReservaDAO
    {
        public string InsertarReserva(Reserva reserva)
        {
            try
            {
                using (var sqlConnection = ConnectionFactory.GetConnection())
                {

                    sqlConnection.Open();
                    string sqlQuery = "INSERT INTO PSR_RESERVA(dtFecReserva,intCodLocal,blnEstado,intCantidadPersonas,intCliente) VALUES (@dtFecReserva,@intCodLocal,@blnEstado,@intCantidadPersonas,@intCliente)";
                    sqlConnection.Execute(sqlQuery,
                        new
                        {
                            dtFecReserva = reserva.dtFecReserva,
                            intCodLocal = reserva.intCodLocal,
                            blnEstado = reserva.blnEstado,
                            intCantidadPersonas = reserva.intCantidadPersonas,
                            intCliente = reserva.intCliente
                        });

                    sqlConnection.Close();
                    return "";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string ActualizarReserva(Reserva reserva)
        {
            try
            {
                using (var sqlConnection = ConnectionFactory.GetConnection())
                {
                    sqlConnection.Open();
                    string sqlQuery = "UPDATE PSR_RESERVA SET dtFecReserva=@dtFecReserva,intCodLocal=@intCodLocal,blnEstado=@blnEstado,intCantidadPersonas=@intCantidadPersonas,intCliente=@intCliente WHERE intReserva=@intReserva";
                    sqlConnection.Execute(sqlQuery,
                        new
                        {
                            intReserva = reserva.IntReserva,
                            dtFecReserva = reserva.dtFecReserva,
                            intCodLocal = reserva.intCodLocal,
                            blnEstado = reserva.blnEstado,
                            intCantidadPersonas = reserva.intCantidadPersonas,
                            intCliente = reserva.intCliente
                        });

                    sqlConnection.Close();
                    return "";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string EliminarReserva(Reserva reserva)
        {
            try
            {
                using (var sqlConnection = ConnectionFactory.GetConnection())
                {

                    sqlConnection.Open();
                    string sqlQuery = "DELETE FROM PSR_RESERVA WHERE intReserva=@intReserva";
                    sqlConnection.Execute(sqlQuery,
                        new
                        {
                            intReserva = reserva.IntReserva
                        });

                    sqlConnection.Close();
                    return "";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public IEnumerable<Reserva> ListarReservas()
        {
            using (var sqlConnection = ConnectionFactory.GetConnection())
            {
                sqlConnection.Open();
                var sqlQuery = string.Format(@"Select * from PSR_RESERVA");
                var result = sqlConnection.Query<Reserva>(sqlQuery);
                sqlConnection.Close();
                return result;
            }
        }

        public Reserva ObtenerReserva(string codigo)
        {
            using (var sqlConnection = ConnectionFactory.GetConnection())
            {
                sqlConnection.Open();
                const string sqlQuery = "select * from psr_reserva where intreserva=@intreserva";
                var reserva = sqlConnection.Query<Reserva>(sqlQuery, new { intreserva = codigo }).FirstOrDefault<Reserva>();

                sqlConnection.Close();

                return reserva;
            }
        }

        public IEnumerable<Reserva> ListarReservasDelDiaCambio()
        {

            /*const string sqlQuery = "begin  set dateformat ymd;"
                        + "select intReserva as 'ID',dtFecReserva as 'Fecha Reserva',"
                        + "PRS_LOCAL.vchnombre as 'Local',"
                        + "IntCantidadPersonas as 'Comenzales',"
                        + "prs_cliente.vchNombre + ' ' + prs_cliente.vchApellido as Cliente,"
                        + "psr_reserva.vchUsuario as 'Usuario' "
                        + "from psr_reserva,PRS_LOCAL,prs_cliente "
                        + "where psr_reserva.intcodlocal=PRS_LOCAL.intCodlocal "
                        + "and psr_reserva.intCliente=prs_cliente.IntCliente "
                        + "and dtFecReserva>@minutos; end";*/

            const string sqlQuery = "begin set dateformat ymd;"
                        + "select * "
                        + "from PSR_reserva "
                        + "where dtFecReserva>@minutos; end";
            
            String strfechahoy = DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss");
            
            using (var sqlConnection = ConnectionFactory.GetConnection())
            {
                sqlConnection.Open();
                var result = sqlConnection.Query<Reserva>(sqlQuery, new { minutos = strfechahoy });

                sqlConnection.Close();

                return result;
            }
    }


        public List<Reserva> ListarReservasRestaurante(string codigo)
        {

            string sql = "begin  set dateformat ymd;"
                        + "select intReserva,dtFecReserva,intCodLocal,blnEstado,intCantidadPersonas,intCliente "
                        + "from psr_reserva "
                        + "where intCodLocal=" + codigo + " ORDER BY dtFecReserva; end";
            
            Reserva OReserva;
            List<Reserva> lista = new List<Reserva>();


            ClienteDAO clienteDAO = new ClienteDAO();
            LocalDAO localDAO = new LocalDAO();

            using (SqlConnection con = new SqlConnection(ConnectionFactory.Connectionstring))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    SqlDataReader rd;

                    rd = com.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    while (rd.Read())
                    {

                        OReserva = new Reserva();
                        OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1);
                        OReserva.intCodLocal = rd.GetInt32(2);
                        OReserva.blnEstado = rd.GetString(3);
                        OReserva.intCantidadPersonas = rd.GetInt32(4);
                        OReserva.intCliente = rd.GetInt32(5);

                        OReserva.cliente = clienteDAO.ObtenerCliente(OReserva.intCliente);
                        OReserva.local = localDAO.ObtenerLocal(OReserva.intCodLocal);
                        
                        lista.Add(OReserva);
                    }
                }
            }


            return lista;
        }
        public List<Reserva> ListarReservasDelDia()
        {

            /*string sql = "begin  set dateformat ymd;"
                        + "select intReserva as 'ID',dtFecReserva as 'Fecha Reserva',"
                        + "PRS_LOCAL.vchnombre as 'Local',"
                        + "IntCantidadPersonas as 'Comenzales',"
                        + "prs_cliente.vchNombre + ' ' + prs_cliente.vchApellido as Cliente,"
                        + "psr_reserva.vchUsuario as 'Usuario' "
                        + "from psr_reserva,PRS_LOCAL,prs_cliente "
                        + "where psr_reserva.intcodlocal=PRS_LOCAL.intCodlocal "
                        + "and psr_reserva.intCliente=prs_cliente.IntCliente "
                        + "and dtFecReserva>'" + DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss") + "'; end";*/

            string sql = "begin  set dateformat ymd;"
                        + "select intReserva,dtFecReserva,vchUsuario,intCodLocal,blnEstado,intCantidadPersonas,intCliente "
                        + "from psr_reserva "
                        + "where dtFecReserva>'" + DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss") + "'; end";
            
            Reserva OReserva;
            List<Reserva> lista = new List<Reserva>();


            ClienteDAO clienteDAO = new ClienteDAO();
            LocalDAO localDAO = new LocalDAO();

            using (SqlConnection con = new SqlConnection(ConnectionFactory.Connectionstring))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    SqlDataReader rd;

                    rd = com.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    while (rd.Read())
                    {

                        OReserva = new Reserva();
                        OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1);
                        OReserva.intCodLocal = rd.GetInt32(2);
                        OReserva.blnEstado = rd.GetString(3);
                        OReserva.intCantidadPersonas = rd.GetInt32(4);
                        OReserva.intCliente = rd.GetInt32(5);

                        OReserva.cliente = clienteDAO.ObtenerCliente(OReserva.intCliente);
                        OReserva.local = localDAO.ObtenerLocal(OReserva.intCodLocal);
                        
                        /*OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1).ToString("dd/MM/yyyy HH:mm");
                        OReserva.StrLocal = rd.GetString(2);
                        OReserva.intCantidadPersonas = rd.GetInt32(3);
                        OReserva.chrCliente = rd.GetString(4);
                        OReserva.vchUsuario = rd.GetString(5);*/
                        lista.Add(OReserva);
                    }
                }
            }


            return lista;
        }



        public List<Reserva> ListarReservasCliente(string codigo)
        {

            string sql = "select intReserva,dtFecReserva,intCodLocal,blnEstado,intCantidadPersonas " +
                            " from psr_reserva where intCliente = " + codigo;

            Reserva OReserva;
            List<Reserva> lista = new List<Reserva>();
            LocalDAO localDAO = new LocalDAO();

            using (SqlConnection con = new SqlConnection(ConnectionFactory.Connectionstring))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    SqlDataReader rd;

                    rd = com.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    while (rd.Read())
                    {

                        OReserva = new Reserva();
                        OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1);
                        OReserva.intCodLocal = rd.GetInt32(2);
                        OReserva.blnEstado = rd.GetString(3);
                        OReserva.intCantidadPersonas = rd.GetInt32(4);
                        OReserva.local = localDAO.ObtenerLocal(OReserva.intCodLocal);
                        lista.Add(OReserva);
                    }
                }
            }


            return lista;
        }

    }
}